Write a cursor which displays the details of project having budget more than 100000 from ‘Computer Science Department’.

RDBMS SOLUTION

ASSIGNMENT 5 

SET A Q.2 SOLUTION

Write a cursor which displays the details of project having budget more than 100000 from ‘Computer Science Department’.

Source code:

    

    /*set a q 2 solution

    Explaination of the code will be at the end*/

declare

pno pproject.pno%type;

pname pproject.pname%type;

dno department.dno%type;

dname department.dname%type;

status pproject.status%type;

cursor p_det is select pno,pname,dno,dname,status

from department d,pproject p,p_d pd

where p.pno=pd.project_number and d.dno=pd.department_number;

begin

open p_det;

loop 

Exit when p_det%notfound;

fetch p_det into pno,pname,dno,dname,status;

dbms_output.put_line(pno||'     ' ||pname||'    ' ||dno||'  ' ||dname||'   ' ||status);

end loop;

close p_det;

end;

/*Explaination:

 The given code is a PL/SQL code block that fetches data related to projects and departments from three tables: pproject, department, and p_d. It uses a cursor to retrieve project number, project name, department number, department name, and project status for all records that exist in the intersection of these tables. 

Let's break down the code:

1. Declaration of variables: 

The variables pno, pname, dno, dname, and status are declared with their respective data types that are defined based on the corresponding columns' data types in the tables pproject and department.

2. Cursor declaration: 

The cursor p_det is defined to select project number, project name, department number, department name, and project status from the tables department d, pproject p, and p_d pd, where p.pno=pd.project_number and d.dno=pd.department_number.

3. The code block begins with the open statement for the cursor p_det.

4. A loop is initiated using the loop keyword. The loop will continue until the p_det%notfound condition is met, which indicates that all rows have been fetched from the cursor.

5. The fetch statement retrieves the next row from the cursor and assigns the values of pno, pname, dno, dname, and status to the corresponding variables.

6. The dbms_output.put_line statement displays the values of pno, pname, dno, dname, and status on the console separated by spaces.

7. The end loop statement ends the loop.

8. The close statement closes the cursor.

The final result of this code is to display the project number, project name, department number, department name, and project status for all records that exist in the intersection of the tables pproject, department, and p_d on the console.*/

   

    

 Download code         next